package com.arong.swing.db.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.arong.swing.db.JDBCUtils;
import com.arong.swing.db.entity.CrCheckevent;
import com.arong.swing.db.entity.PagingInfo;
import com.arong.swing.db.entity.PagingResult;
import com.arong.swing.exception.AppException;
import com.arong.swing.exception.SysException;
import com.arong.swing.util.StringUtil;
import com.arong.swing.util.UUIDUtils;
public class CrCheckeventSDAO  extends BaseDAO {
	public CrCheckeventSDAO(){
		this(DB_CHECKRECORD);
	}
	public CrCheckeventSDAO(String dbFile){
		super(dbFile);
	}
	public CrCheckeventSDAO(JDBCUtils jdbcUtils){
		super(jdbcUtils);
	}
	public String getCreateTableSql(){
		return "CREATE TABLE IF NOT EXISTS "+
				"CR_CHECKEVENT("+
						 " ID  VARCHAR2(32) PRIMARY KEY,"+
						 " USER_ID VARCHAR2(32),"+
						 " EQUIPT_ID VARCHAR2(32), "+
						 " START_TIME DATE,"+
						 " END_TIME DATE,"+
						 " CHECKPOINT_ID  VARCHAR2(32),"+
						 " CHECK_USER  VARCHAR2(32),"+
						 " ADDRES  VARCHAR2(32),"+
						 " PCDDX NUMBER(10,5),"+
						 " PCDDY NUMBER(10,5),"+
						 " REMARK VARCHAR2(512)"+
						 ")";
		
	}
	public void createTabbleIndex(){
		try {
			jdbcUtils.execute("CREATE INDEX IF NOT EXISTS INDEX_TIME_EVENT on CR_CHECKEVENT(START_TIME)");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public  long count() throws SysException,AppException {
		long i=0;
		try {
		 	  i= jdbcUtils.queryNumValue("select count(*) from CR_CHECKEVENT");
		} catch (SQLException e) {
			throw new SysException("add error", e);
		}
		return i;
	}
	public  CrCheckevent queryMaxTimeBean() throws SysException,AppException {
		CrCheckevent res=null;
	    try {
	    	StringBuffer sql = new StringBuffer("SELECT ");
			sql.append(" a.ID,a.USER_ID,a.EQUIPT_ID,a.START_TIME,a.END_TIME,a.CHECKPOINT_ID,a.CHECK_USER,a.ADDRES,a.PCDDX,a.PCDDY,a.REMARK");
			sql.append(" from CR_CHECKEVENT a ORDER BY START_TIME asc LIMIT 0,1");
			res=  jdbcUtils.queryBean(sql.toString(), CrCheckevent.class);
		} catch (SQLException e) {
			throw new SysException("queryBean对象查询 error",e);
		}
		return   res;
	}
	/**
	 * 插入
	 * @param entity
	 * @return
	 * @throws SysException, AppException
	 */
	public  int insert(CrCheckevent entity) throws SysException,AppException {
		int i=0;
		try {
		 		    if (StringUtil.isBlank(entity.getId())) {
				entity.setId(getSeqValue());
			}
		 			i= jdbcUtils.insert(entity);
		} catch (SQLException e) {
			throw new SysException("add error", e);
		}
		return i;
	}

	/**
	 * 修改
	 * @param entity
	 * @return
	 * @throws SysException, AppException
	 */
	public  int update(CrCheckevent entity) throws SysException,AppException {
		int i=0;
		try {
			i=jdbcUtils.update(entity, "id");
		} catch (SQLException e) {
			throw new SysException("update error",e);
		}
		return i;
	}

	/**
	 * 删除
	 * @param entity
	 * @return
	 * @throws SysException, AppException
	 */
	public  int delete(CrCheckevent entity) throws SysException,AppException {
		int i = 0;
		try {
			i=jdbcUtils.execute("DELETE FROM CR_CHECKEVENT WHERE ID='"
					    +entity.getId()
		    		    +"'"
			);
		} catch (SQLException e) {
			throw new SysException("delete error", e);
		}
		return i;
	}

	/**
	 * 分页查询
	 * @param sql
	 * @param pagingInfo
	 * @param entity
	 * @return 
	 * @throws SysException, AppException
	 */
	public  PagingResult<CrCheckevent> queryList(PagingInfo pagingInfo, CrCheckevent entity)
			throws SysException,AppException {
		PagingResult<CrCheckevent> res=null;
	    try {
			res= jdbcUtils.queryList(getSql(entity), CrCheckevent.class,
				pagingInfo, getParams(entity));
		} catch (SQLException e) {
			throw new SysException("queryList分页查询 error",e);
		}
		return   res;
	}

	/**
	 * 查询 集合
	 * @param entity
	 * @return
	 * @throws SysException, AppException
	 */
	public   List<CrCheckevent> queryList(CrCheckevent entity) throws SysException,AppException {
	    List<CrCheckevent> res=null;
	    try {
			res= jdbcUtils.queryList(getSql(entity), CrCheckevent.class,
			getParams(entity));
		} catch (SQLException e) {
			throw new SysException("queryList集合查询 error",e);
		}
		return   res;
	}

	/**
	 * 查询对象
	 * @param entity
	 * @return
	 * @throws SysException, AppException
	 */
	public  CrCheckevent queryBean(CrCheckevent entity) throws SysException,AppException {
		CrCheckevent res=null;
	    try {
			res=  jdbcUtils.queryBean(getSql(entity), CrCheckevent.class,
				getParams(entity));
		} catch (SQLException e) {
			throw new SysException("queryBean对象查询 error",e);
		}
		return   res;
	}

	/**
	 * 获取查询条件
	 * @return
	 */
	private Object[] getParams(CrCheckevent crCheckevent) {
		List<Object> qryList = new ArrayList<Object>();
		if (crCheckevent.getId() != null) {
			qryList.add(crCheckevent.getId());
		}
		if (crCheckevent.getUserId() != null) {
			qryList.add(crCheckevent.getUserId());
		}
		if (crCheckevent.getEquiptId() != null) {
			qryList.add(crCheckevent.getEquiptId());
		}
		if (crCheckevent.getStartTime() != null) {
			qryList.add(crCheckevent.getStartTime());
		}
		if (crCheckevent.getEndTime() != null) {
			qryList.add(crCheckevent.getEndTime());
		}
		if (crCheckevent.getCheckpointId() != null) {
			qryList.add(crCheckevent.getCheckpointId());
		}
		if (crCheckevent.getCheckUser() != null) {
			qryList.add(crCheckevent.getCheckUser());
		}
		if (crCheckevent.getAddres() != null) {
			qryList.add(crCheckevent.getAddres());
		}
		if (crCheckevent.getPcddx() != null) {
			qryList.add(crCheckevent.getPcddx());
		}
		if (crCheckevent.getPcddy() != null) {
			qryList.add(crCheckevent.getPcddy());
		}
		if (crCheckevent.getRemark() != null) {
			qryList.add(crCheckevent.getRemark());
		}
		return super.setQryList(qryList);
	}

	/**
	 * 获取sql
	 * @return
	 */
	private String getSql(CrCheckevent crCheckevent) {
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append(" a.ID,a.USER_ID,a.EQUIPT_ID,a.START_TIME,a.END_TIME,a.CHECKPOINT_ID,a.CHECK_USER,a.ADDRES,a.PCDDX,a.PCDDY,a.REMARK");
		sql.append(" from CR_CHECKEVENT a where 1=1");
		if (crCheckevent.getId() != null) {
			sql.append(" and ID=?");
		}
		if (crCheckevent.getUserId() != null) {
			sql.append(" and USER_ID=?");
		}
		if (crCheckevent.getEquiptId() != null) {
			sql.append(" and EQUIPT_ID=?");
		}
		if (crCheckevent.getStartTime() != null) {
			sql.append(" and START_TIME=?");
		}
		if (crCheckevent.getEndTime() != null) {
			sql.append(" and END_TIME=?");
		}
		if (crCheckevent.getCheckpointId() != null) {
			sql.append(" and CHECKPOINT_ID=?");
		}
		if (crCheckevent.getCheckUser() != null) {
			sql.append(" and CHECK_USER=?");
		}
		if (crCheckevent.getAddres() != null) {
			sql.append(" and ADDRES=?");
		}
		if (crCheckevent.getPcddx() != null) {
			sql.append(" and PCDDX=?");
		}
		if (crCheckevent.getPcddy() != null) {
			sql.append(" and PCDDY=?");
		}
		if (crCheckevent.getRemark() != null) {
			sql.append(" and REMARK=?");
		}
		return sql.toString();
	}
	
	public static void main(String[] args) throws Exception, AppException {
		
		final JDBCUtils jdbcUtils = new JDBCUtils("anrongCK.db");
		CrCheckeventSDAO dao =new CrCheckeventSDAO(jdbcUtils);
		
		CrCheckevent entity = new CrCheckevent();
		entity.setEquiptId("2222222");
		entity.setAddres("wwww");
		entity.setEndTime(new Timestamp(System.currentTimeMillis()));
		dao.insert(entity);
		entity.setAddres("ssss");
		dao.update(entity);
		CrCheckevent queryEntity =new CrCheckevent();
		queryEntity.setId(entity.getId());
		queryEntity = dao.queryBean(queryEntity);
		System.out.println(queryEntity.getAddres());
		
		/** 查询 */
		List<CrCheckevent> list = dao.queryList(new CrCheckevent());
		System.out.println(list.size());
		
		/** 批量事务操作 */
		IBatchDeal batchDeal  =new IBatchDeal() {
			@Override
			public int doBatchDeal(Connection conn) throws Exception {
				CrCheckevent entity = new CrCheckevent();
				entity.setId(UUIDUtils.uuid32());
				entity.setEquiptId("444444");
				entity.setAddres("xxxxxxxx");
				entity.setEndTime(new Timestamp(System.currentTimeMillis()));
				jdbcUtils.insert(conn, entity);
				return 0;
			}
			
		};
		dao.batchCommit(batchDeal);
		
		/** 分页查询 */
		PagingInfo pageInfo =JDBCUtils.getPageInfo("1","5");
		PagingResult<CrCheckevent> pages = dao.queryList(pageInfo, new CrCheckevent());
		System.out.println(pages.getResultList().size());
	}
}

